import time
import pymysql.cursors
import numpy as np
import pymysql
import pandas as pd

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='localhost',
            user='root',
            password='123456',
            database='scenic',
            port=3307,
            charset='utf8mb4' 
        )
        
    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql="""
        SELECT t.tourist_agency_name, rel.id_no, LEFT(rel.id_no, 2) as province_code, DAYOFWEEK(gate.create_time) as non_weekend, CAST(SUBSTRING(rel.id_no, 7, 4) as UNSIGNED) as birth_year
        FROM ticket_order_user_rel rel JOIN ticket_order t on t.id = rel.order_id JOIN order_user_gate_rel gate on gate.ticket_rel_id = rel.id 
        WHERE t.tourist_agency_name != '' and t.pay_time != ''
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        
        df['non_weekend'] = df['non_weekend'].apply(lambda x: 1 if x not in [1, 7] else 0)
        
        df['valid_id'] = df['id_no'].apply(lambda x: 1 if x and str(x).strip() !='' else 0)
        
        df['elderly_ratio'] = df.apply(lambda x : 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan, axis=1)
        
        df['out_province_ratio'] = df.apply(
            lambda x: 1 if (x['valid_id'] and x['province_code'] !=44) else 0 if x['valid_id'] else np.nan,axis=1
        )
       
        
        # print(df.head)
        
        result = df.groupby(['tourist_agency_name']).agg(
            total_visitors= ('id_no', 'count'),
            valid_visitors= ('valid_id', 'sum'),
            out_province= ('out_province_ratio', 'sum'),
            elderly= ('elderly_ratio', 'sum'),
            non_weekend_ratio= ('non_weekend', 'mean')
        ).reset_index()
        
        result['out_province_ratio'] = result['out_province'] / result['valid_visitors'].replace(0, np.nan)
        result['elderly_ratio'] = result['elderly'] / result['valid_visitors'].replace(0, np.nan)
        
        result = result.drop(['out_province', 'elderly'], axis=1)
        result['out_province_ratio'] = result['out_province_ratio'].fillna(0)
        result['elderly_ratio'] = result['elderly_ratio'].fillna(0)
        # print(result.head)
        result.to_csv('scenic_data.csv')
        
        
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()